﻿Imports System.Data.OleDb
Public Class Datos
    Protected Conexion As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Yanapay;Data Source=notebook\SQLEXPRESS")
    Protected comando As New OleDb.OleDbCommand()
    Protected consulta As New OleDb.OleDbCommand()

    'Public Conexion As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FacultadMatematicaAplicada;Data Source=RAMONS-LAPTOP\SQLEXPRESS")
    Public Sub New()
        comando.CommandType = CommandType.StoredProcedure
        comando.Connection = Me.Conexion

        consulta.CommandType = CommandType.StoredProcedure
        consulta.Connection = Me.Conexion
    End Sub


    Public Function ObtenerSelect(ByVal procedimientoAlmacenado As String, ByVal nombreTabla As String) As DataSet
        Conexion.Open()
        consulta.CommandText = procedimientoAlmacenado
        Dim TablaDS As New DataSet()
        Dim tabla_bodega As New OleDbDataAdapter(consulta)
        tabla_bodega.Fill(TablaDS, nombreTabla)
        Conexion.Close()
        Return TablaDS
    End Function

    Public Function ObtenerSelect(ByVal procedimientoAlmacenado As String, ByVal nombreTabla As String _
                                  , ByVal fechaDesde As DateTime, ByVal fechaHasta As DateTime) As DataSet
        Conexion.Open()
        Dim consultaFecha As New OleDb.OleDbCommand()
        consultaFecha.CommandType = CommandType.StoredProcedure
        consultaFecha.Connection = Me.Conexion
        consultaFecha.CommandText = procedimientoAlmacenado

        consultaFecha.Parameters.Add(New OleDbParameter("@paramDesdeFecha", fechaDesde))
        consultaFecha.Parameters.Add(New OleDbParameter("@paramHastaFecha", fechaHasta))

        Dim TablaDS As New DataSet()
        Dim tabla_bodega As New OleDbDataAdapter(consultaFecha)
        tabla_bodega.Fill(TablaDS, nombreTabla)
        Conexion.Close()
        Return TablaDS
    End Function


    Public Function ObtenerSelect(ByVal procedimientoAlmacenado As String, ByVal nombreTabla As String _
                                  , ByVal paramSQL As String, ByVal paramVB As String) As DataSet
        Conexion.Open()
        Dim consultaExpediente As New OleDb.OleDbCommand()
        consultaExpediente.CommandType = CommandType.StoredProcedure
        consultaExpediente.Connection = Me.Conexion
        consultaExpediente.CommandText = procedimientoAlmacenado

        consultaExpediente.Parameters.Add(New OleDbParameter(paramSQL, paramVB))

        Dim TablaDS As New DataSet()
        Dim tabla_bodega As New OleDbDataAdapter(consultaExpediente)
        tabla_bodega.Fill(TablaDS, nombreTabla)
        Conexion.Close()
        Return TablaDS
    End Function

    Public Function ObtenerSelect(ByVal procedimientoAlmacenado As String, ByVal nombreTabla As String _
                              , ByVal paramSQL As ArrayList, ByVal paramVB As ArrayList) As DataSet
        Conexion.Open()
        Dim consultaExpediente As New OleDb.OleDbCommand()
        consultaExpediente.CommandType = CommandType.StoredProcedure
        consultaExpediente.Connection = Me.Conexion
        consultaExpediente.CommandText = procedimientoAlmacenado

        For i As Integer = 0 To paramSQL.Count - 1
            consultaExpediente.Parameters.Add(New OleDbParameter _
                                              (paramSQL.Item(i).ToString, _
                                               paramVB.Item(i).ToString))
        Next

        Dim TablaDS As New DataSet()
        Dim tabla_bodega As New OleDbDataAdapter(consultaExpediente)
        tabla_bodega.Fill(TablaDS, nombreTabla)
        Conexion.Close()
        Return TablaDS
    End Function

    Protected Function ejecutarComando()
        Me.Conexion.Open()
        comando.ExecuteNonQuery()
        Me.Conexion.Close()
        Return True
    End Function


    Public Function obtenerValor(ByVal fecha As Date, ByVal cuit As String _
                                 , ByVal Tipo As Integer, ByVal paramOutput As String _
                                 , ByVal procedimientoAlmacenado As String) As String
        Dim consultavalor As New OleDbCommand()

        consultavalor.Connection = Conexion
        consultavalor.CommandType = CommandType.StoredProcedure
        consultavalor.CommandText = procedimientoAlmacenado

        Dim RetValue As OleDbParameter = consultavalor.Parameters.Add("RetValue", OleDbType.Integer)
        RetValue.Direction = ParameterDirection.ReturnValue

        Dim paramFechaIn As OleDbParameter = consultavalor.Parameters.Add("@paramFecha", OleDbType.VarChar, 10)
        paramFechaIn.Direction = ParameterDirection.Input

        Dim paramCuitIn As OleDbParameter = consultavalor.Parameters.Add("@paramCuit", OleDbType.VarChar, 10)
        paramCuitIn.Direction = ParameterDirection.Input

        Dim paramTipoIn As OleDbParameter = consultavalor.Parameters.Add("@paramTipo", OleDbType.Integer)
        paramTipoIn.Direction = ParameterDirection.Input


        Dim paramOut As OleDbParameter = consultavalor.Parameters.Add(paramOutput, OleDbType.Integer)
        paramOut.Direction = ParameterDirection.Output

        paramFechaIn.Value = fecha
        paramCuitIn.Value = cuit
        paramTipoIn.Value = Tipo

        Conexion.Open()
        Dim myReader As OleDbDataReader = consultavalor.ExecuteScalar()

        Conexion.Close()

        Return paramOut.Value
    End Function

    Public Function obtenerValor(ByVal id_pedido As Integer _
                             , ByVal paramOutput As String _
                             , ByVal procedimientoAlmacenado As String) As String
        Dim consultavalor As New OleDbCommand()

        consultavalor.Connection = Conexion
        consultavalor.CommandType = CommandType.StoredProcedure
        consultavalor.CommandText = procedimientoAlmacenado

        Dim RetValue As OleDbParameter = consultavalor.Parameters.Add("RetValue", OleDbType.Integer)
        RetValue.Direction = ParameterDirection.ReturnValue


        Dim paramIdIn As OleDbParameter = consultavalor.Parameters.Add("@paramId", OleDbType.VarChar, 10)
        paramIdIn.Direction = ParameterDirection.Input

        Dim paramOut As OleDbParameter = consultavalor.Parameters.Add(paramOutput, OleDbType.Integer)
        paramOut.Direction = ParameterDirection.Output


        paramIdIn.Value = id_pedido

        Conexion.Open()
        Dim myReader As OleDbDataReader = consultavalor.ExecuteScalar()

        Conexion.Close()

        Return paramOut.Value
    End Function
End Class
